<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/7
 * Time: 17:32
 */
namespace Admin\Model;
use Think\Model;

class ReportModel extends Model{
    protected $trueTableName = 'bypal_borrow_info';

    /**
     * 个人申请报文
     * @return mixed
     */
    public function get_borrow_apply_info($start_time,$end_time,$borrow_status,$select_name){
        $sql="select t1.borrow_id,t1.borrow_money,(t1.borrow_loanperiod+t4.delay_time) borrow_loanperiod,FROM_UNIXTIME(t1.borrow_createdate,'%Y%m%d') borrow_createdate,
        t2.cust_name,t2.cust_identity,t3.ls_value,t1.borrow_stateid,t1.contract_id
        from bypal_borrow_info t1 left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
        left join bypal_cust_loanstatus t3 on t1.borrow_stateid=t3.ls_id
        left join bypal_facilitator_trade t4 on t1.borrow_id=t4.trade_contractnumber
        left join bypal_borrow_buinessinfo t5 on t1.borrow_id=t5.binfo_borrowid where 1=1 ";
        if($start_time!=''&&$end_time!=''){
            $sql.=" and t5.binfo_opendate between $start_time and $end_time";
        }
        if($borrow_status!=''){
            $sql.=" and t1.borrow_stateid=$borrow_status";
        }
        if ($select_name != '') 
        {
            $sql .= " and t2.cust_name like '%$select_name%'";
        }
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function update_contract($array){
        $sql="update bypal_borrow_info set contract_id=".$array['contract_id']." where borrow_id=".$array['borrow_id'];
        $Model= new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    /**
     * 个人基本信息报文
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function get_person_info($start_time,$end_time,$select_name){
        $sql="select t1.cust_name,t1.cust_identity,IFNULL(t1.cust_sex,9) cust_sex,IFNULL(t3.m_code,90) marry_status,IFNULL(t4.edu_code,99) education_status,
        IFNULL(t5.deg_code,9) degree_status,t1.cust_phone,t1.cust_email,t2.communication_province,t2.communication_city,t2.communication_district,
        t2.communication_address,IFNULL(t2.communication_postcode,999999) communication_postcode,t6.first_linkman_name,t15.r_code first_linkman_type,t6.first_linkman_phone,t6.second_linkman_name,
        t14.r_code second_linkman_type,t6.second_linkman_phone,IFNULL(t8.pos_code,'Z') pos_code,IFNULL(t7.company_name,'暂缺') company_name,
        t7.company_province,t7.company_city,t7.company_district,t7.company_address,IFNULL(t9.du_code,9) du_code,IFNULL(t10.ti_code,0) ti_code,
        t2.live_province,t2.live_city,t2.live_district,t2.live_address,IFNULL(t2.live_postcode,999999) live_postcode,IFNULL(t11.li_code,9) li_code,
        IFNULL(t12.code,'Z') industry_code
        from bypal_borrow_info t13
        left join bypal_cust_info t1 on t13.borrow_custid=t1.cust_id
        left join bypal_cust_detailinformation t2 on t1.cust_id=t2.cust_id
        left join bypal_cust_marital t3 on t2.marry_status_id=t3.m_id
        left join bypal_cust_education t4 on t2.education_status_id=t4.edu_id
        left join bypal_cust_degree t5 on t2.degree_status_id=t5.deg_id
        left join bypal_cust_linkman t6 on t1.cust_id =t6.cust_id
        left join bypal_cust_workdetail t7 on t1.cust_id=t7.cust_id
        left join bypal_cust_position t8 on t7.profession_status_id=t8.pos_id
        left join bypal_cust_duty t9 on t7.duty_status=t9.du_id
        left join bypal_cust_title t10 on t7.title_status=t10.ti_id
        left join bypal_cust_livestatus t11 on t2.live_status=t11.li_id
        left join bypal_profession_info t12 on t7.company_industry=t12.id
        left join bypal_cust_relation t15 on t6.first_linkman_type=t15.r_id
        left join bypal_cust_relation t14 on t6.second_linkman_type=t14.r_id
        left join (select binfo_borrowid,binfo_opendate,count(binfo_id) count_binfo from bypal_borrow_buinessinfo group by buinfo_infotips order by binfo_opendate desc) t16 on t13.borrow_id=t16.binfo_borrowid
        where 1=1 and t1.cust_name is not null and t16.count_binfo=1";

        if($start_time!=''&&$end_time!=''){
            $sql.=" and t16.binfo_opendate between '$start_time' and '$end_time'";
        }

        if ($select_name != '') 
        {
            $sql .= " and t1.cust_name like '%$select_name%'";
        }
        $sql.=" group by t13.borrow_custid";

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }


    /**
     * 线下贷款申请信息报文
     * @param $start_time
     * @param $end_time
     */

    public function get_apply_unline($start_time,$end_time,$borrow_status,$select_name){
        $sql="select id,borrow_id,cust_name,identity_type,identity_number,apply_type,apply_money,apply_period,apply_date
              ,apply_status,create_time from bypal_report_applyloan where 1=1";

        if($start_time!=''&&$end_time!=''){
            $sql.=" and apply_date between '$start_time' and '$end_time'";
        }
        if($borrow_status!=''){
            $sql.=" and apply_status=$borrow_status";
        }
        if ($select_name != '') 
        {
            $sql .= " and cust_name like '%$select_name%'";
        }

        $sql.=" order by apply_date desc";

        $Model= new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    /**
     * 新增线下贷款申请信息报文
     */
    public function insert_apply_unline($array){
        $sql="insert into bypal_report_applyloan(borrow_id,cust_name,identity_type,identity_number,apply_type,apply_money,apply_period,apply_date
              ,apply_status,create_time) values('".$array['borrow_id']."','".$array['cust_name']."','".$array['identity_type']."',
              '".$array['identity_number']."','".$array['apply_type']."','".$array['apply_money']."','".$array['apply_period']."',
              '".$array['apply_date']."','".$array['apply_status']."',now())";

        $Model= new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    /**
     * 修改线下贷款申请信息报文
     * @param $array
     * @return false|int
     */
    public function update_apply_unline($array){
        $sql="update bypal_report_applyloan set borrow_id='".$array['borrow_id']."',cust_name='".$array['cust_name']."',
        identity_type='".$array['identity_type']."',identity_number='".$array['identity_number']."',apply_type='".$array['apply_type']."',
        apply_money='".$array['apply_money']."',apply_period='".$array['apply_period']."',apply_date='".$array['apply_date']."',
        apply_status='".$array['apply_status']."' where borrow_id='".$array['borrow_id']."'";
        $Model= new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    /**
     * 根据借款编号查询线下贷款申请信息
     * @param $borrow_id
     * @return int
     */
    public function select_one_apply_unline($borrow_id){
        $sql="select id from bypal_report_applyloan where borrow_id='$borrow_id'";
        $Model= new \Think\Model();
        $values=$Model->query($sql);
        $count=count($values);
        return $count;
    }


    /**
     * 贷款基本信息报文
     * @return mixed
     */
    public function get_loan_info($start_time,$end_time,$select_name,$page=0,$rows=10)
    {
        $select_sql="select t1.binfo_borrowid,FROM_UNIXTIME(t1.binfo_opendate,'%Y%m%d') binfo_opendate,FROM_UNIXTIME(t1.buinfo_contractdown,'%Y%m%d') buinfo_contractdown,
        round(t1.binfo_grantdegree) binfo_grantdegree,round(t1.binfo_sgrantdegree) binfo_sgrantdegree,t2.borrow_loanperiod,t4.delay_time,t3.cust_name,t3.cust_identity,t3.cust_id,t1.binfo_opendate opendate,
        t1.binfo_ensuretype,t1.binfo_repayhz,t1.binfo_state,t5.principal,t5.interest,t5.period_recovery,t5.repay_date,t5.period_number,t5.end_date,
        FROM_UNIXTIME(t2.borrow_passtime,'%Y%m%d') borrow_passtime,t1.buinfo_workststus,t1.buinfo_repaydown,round(t2.borrow_money) borrow_money,t2.contract_id,t2.borrow_id
        from bypal_borrow_buinessinfo t1 left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id
        left join bypal_cust_info t3 on t2.borrow_custid=t3.cust_id
        left join bypal_facilitator_trade t4 on t1.binfo_borrowid=t4.trade_contractnumber
        left join
        (select repay_detail_id,binfo_id,principal,interest,period_recovery,repay_date,period_number,max(end_date) end_date
        from bypal_repay_detail where end_date between '$start_time' and '$end_time' and principal!=0 group by binfo_id) t5
        on t1.binfo_borrowid=t5.binfo_id where (t5.repay_detail_id is not null or t1.binfo_state=3) and t1.binfo_opendate<'$end_time'";
        if ($select_name != '') 
        {
            $select_sql .= " and t3.cust_name like '%$select_name%'";
        }
        if($page!=0) {
            $offset = ($page - 1) * $rows;
            $limit = " limit $offset," . $rows;
            $select_sql .= " order by t1.binfo_opendate " . $limit;
        }else{
            $select_sql .= " order by t1.binfo_opendate ";
        }
        $Model = new \Think\Model();
        $values = $Model->query($select_sql);
        return $values;
    }

    public function get_loan_info_count($start_time,$end_time,$select_name)
    {
        $select_sql="select count(t1.binfo_borrowid) count
        from bypal_borrow_buinessinfo t1 left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id
        left join bypal_cust_info t3 on t2.borrow_custid=t3.cust_id
        left join
        (select repay_detail_id,binfo_id,principal,interest,period_recovery,repay_date,period_number,max(end_date) end_date
        from bypal_repay_detail where end_date between '$start_time' and '$end_time' and principal!=0 group by binfo_id) t5
        on t1.binfo_borrowid=t5.binfo_id where (t5.repay_detail_id is not null or t1.binfo_state=3) and t1.binfo_opendate<'$end_time'";
        if ($select_name != '')
        {
            $select_sql .= " and t3.cust_name like '%$select_name%'";
        }
        $select_sql.=" order by t1.binfo_opendate ";
        $Model = new \Think\Model();
        $values = $Model->query($select_sql);
        $count=$values[0]["count"];
        return $count;
    }


    /**
     * 贷款基本信息报文(线下)
     * @return mixed
     */
    public function get_loan_info_underline($start_time,$end_time,$select_num,$select_name) 
    {
        $select_sql = "select * from bypal_report_loaninfo where 1=1";
        if ($start_time != '') 
        {
            $startTime = explode("-",$start_time);
            $start = $startTime[0] . $startTime[1] . $startTime[2];
            $select_sql .= " and update_time >= '$start'";
        }
        if ($end_time != '') 
        {
            $endTime = explode("-",$end_time);
            $end = $endTime[0] . $endTime[1] . $endTime[2];
            $select_sql .= " and update_time <= '$end'";
        }
        if ($select_num != '') 
        {
            $select_sql .= " and loaninforeport_contractnum like '%$select_num%'";
        }
        if ($select_name != '') 
        {
            $select_sql .= " and loaninforeport_name like '%$select_name%'";
        }

        $select_sql .= " order by update_time DESC";

        $Model = new \Think\Model();
        $values = $Model->query($select_sql);
        return $values;
    }

    /**
     * 查询个人基础报文（线下）
     * @param $start_time
     * @param $end_time
     * @param $cust_name
     * @return mixed
     */
    public function get_person_unline($start_time,$end_time,$cust_name){
        $sql="select * from bypal_report_personinfo where 1=1";
        if($start_time!='' && $end_time !=''){
            $sql.=" and create_time between '$start_time' and '$end_time'";
        }
        if($cust_name!=''){
            $sql.=" and cust_name like '%$cust_name%'";
        }
        $Model = new \Think\Model();
        $values = $Model->query($sql);
        return $values;
    }

    /**
     * 新增个人基础报文（线下）
     * @param $array
     * @return false|int
     */
    public function insert_person_unline($array){
        $sql="insert into bypal_report_personinfo(cust_name,identity_type,identity_number,cust_sex,birth_date,marry_status,education_status
              ,degree_status,address_phone,telephone,company_phone,cust_email,communication_address,communication_postcode,register_address
              ,mate_name,mate_identitytype,mate_identitynumber,mate_commpany,mate_phone,first_linkman_name,first_linkman_type
              ,first_linkman_phone,second_linkman_name,second_linkman_type,second_linkman_phone,profession_status,company_name
              ,industry_status,company_address,company_postcode,work_date,duty_status,title_status,annual_income,live_address,live_postcode
              ,live_status,create_time) values('".$array['cust_name']."','".$array['identity_type']."','".$array['identity_number']."',
              '".$array['cust_sex']."','".$array['birth_date']."','".$array['marry_status']."','".$array['education_status']."',
              '".$array['degree_status']."','".$array['address_phone']."','".$array['telephone']."','".$array['company_phone']."',
              '".$array['cust_email']."','".$array['communication_address']."','".$array['communication_postcode']."',
              '".$array['register_address']."','".$array['mate_name']."','".$array['mate_identitytype']."','".$array['mate_identitynumber']."',
              '".$array['mate_commpany']."','".$array['mate_phone']."','".$array['first_linkman_name']."','".$array['first_linkman_type']."',
              '".$array['first_linkman_phone']."','".$array['second_linkman_name']."','".$array['second_linkman_type']."',
              '".$array['second_linkman_phone']."','".$array['profession_status']."','".$array['company_name']."','".$array['industry_status']."',
              '".$array['company_address']."','".$array['company_postcode']."','".$array['work_date']."','".$array['duty_status']."',
              '".$array['title_status']."','".$array['annual_income']."','".$array['live_address']."',
              '".$array['live_postcode']."','".$array['live_status']."',now())";
        $Model= new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    /**
     * 修改个人基础报文（线下）
     * @param $array
     * @return false|int
     */
    public function update_person_unline($array){
        $sql="update bypal_report_personinfo set cust_name='".$array['cust_name']."',identity_type='".$array['identity_type']."',
        identity_number='".$array['identity_number']."',cust_sex='".$array['cust_sex']."',birth_date='".$array['birth_date']."',
        marry_status='".$array['marry_status']."',education_status='".$array['education_status']."',degree_status='".$array['degree_status']."',
        address_phone='".$array['address_phone']."',telephone='".$array['telephone']."',company_phone='".$array['company_phone']."',cust_email='".$array['cust_email']."',
        communication_address='".$array['communication_address']."',communication_postcode='".$array['communication_postcode']."',
        register_address='".$array['register_address']."',mate_name='".$array['mate_name']."',mate_identitytype='".$array['mate_identitytype']."',
        mate_identitynumber='".$array['mate_identitynumber']."',mate_commpany='".$array['mate_commpany']."',mate_phone='".$array['mate_phone']."',
        first_linkman_name='".$array['first_linkman_name']."',first_linkman_type='".$array['first_linkman_type']."',
        first_linkman_phone='".$array['first_linkman_phone']."',second_linkman_name='".$array['second_linkman_name']."',
        second_linkman_type='".$array['second_linkman_type']."',second_linkman_phone='".$array['second_linkman_phone']."',
        profession_status='".$array['profession_status']."',company_name='".$array['company_name']."',industry_status='".$array['industry_status']."',
        company_address='".$array['company_address']."',company_postcode='".$array['company_postcode']."',work_date='".$array['work_date']."',
        duty_status='".$array['duty_status']."',title_status='".$array['title_status']."',annual_income='".$array['annual_income']."',
        live_address='".$array['live_address']."',live_postcode='".$array['live_postcode']."',live_status='".$array['live_status']."'
        where id='".$array['id']."'";
        $Model= new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function select_identity_person_unline($id){
        $sql="select id from bypal_report_personinfo where id='$id'";
        $Model= new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }



}